package com.cmfi.dao;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.cmfi.domain.PlanWorkOrder;

/**
 * Spring Data R2DBC repository for the ForkliftPlan entity.
 */
@Mapper
public interface PlanWorkOrderDao {

	@Select("select * from tbl_planworkorder order by createTime DESC limit #{start},#{itemNum}")
	List<PlanWorkOrder> findByPage(long start, long itemNum);

	//重点看一下，forklift_plan_temp这个是什么
	@Select("select * from (select * from tbl_planworkorder where planDate=#{date}) forklift_plan_temp"
			+ "  order by createTime DESC limit #{start},#{itemNum}")
	List<PlanWorkOrder> findByDateAndPage(String date, long start, long itemNum);
	
	//重点看一下，forklift_plan_temp这个是什么，这个方法是一个可以复用，但目前没那么好用的方法
	@Select("select * from (select * from tbl_planworkorder ${conditions}) forklift_plan_temp"
			+ "  order by createTime DESC limit #{start},#{itemNum}")
	List<PlanWorkOrder> findByPageAndFilterConditions(String conditions, long start, long itemNum);

	@Select("select * from tbl_planworkorder order by #{orderParam1} #{orderParam1}")
	List<PlanWorkOrder> sortAndfindAll(long start, long itemNum, String orderParam1, String sortOrder1);

	//字段改一下，不是这么多字段了
	@Insert("insert into tbl_planworkorder(line,forkliftStatus,createTime,updateTime,produceNo,sequence,vinCode,materialCode"
			+ ",materialName,dispatchStatus,planDate,planSequence,planStartDate,onlineFlag,offlineFlag,onlineTime,"
			+ "offlineTime,tightenQuality,tightenNM,bomCode,dynamicOnlineTime31,repositoryCode,planId,planIdFlag,bomDate"
			+ ",bomVersion,tonnage,planEndDate,onlinePerDate,onlinePer,onlineSequence,offlinePerDate,onlinePer24,dynamicOnlineTime) "
			+ " values(#{line},#{forkliftStatus},#{createTime},#{updateTime},#{produceNo},#{sequence},"
			+ "#{vinCode},#{materialCode},#{materialName},#{dispatchStatus},#{planDate},#{planSequence},"
			+ "#{planStartDate},#{onlineFlag},#{offlineFlag},#{onlineTime},#{offlineTime},#{tightenQuality},#{tightenNM},#{bomCode},#{dynamicOnlineTime31},"
			+ "	#{repositoryCode},#{planId},#{planIdFlag},#{bomDate},#{bomVersion},#{tonnage},#{planEndDate},#{onlinePerDate},"
			+ "	#{onlinePer},#{onlineSequence},#{offlinePerDate},#{onlinePer24},#{dynamicOnlineTime})")
	@Options(useGeneratedKeys = true, keyProperty = "id") // 可以把自動生成的id帶過來
	void save(PlanWorkOrder planWorkOrder);

	//应该大部分方法都要改，在这里加一个注释提醒一下自己，记得正式改的时候全部检查一遍
	@Update("update tbl_planworkorder set "
			+ "materialName=#{materialName},"
			+ "tonnage=#{tonnage}"
			+ "where id = #{id}")
	public int update(PlanWorkOrder entity);

	@Select("select * from tbl_planworkorder")
	List<PlanWorkOrder> findAll();

	@Select("select * from tbl_planworkorder where id=#{id}")
	PlanWorkOrder findById(Long id);

	@Select("select * from tbl_planworkorder where isReleased=0 or isReleased is null order by id desc limit 1")
	PlanWorkOrder findFirst();

	@Select("select * from tbl_planworkorder where vinCode=#{vinCode}")
	PlanWorkOrder findByVinCode(String vinCode);

	@Select("select count(1) from tbl_planworkorder where planDate=#{date}")
	Long countByPlanDate(String date);

	@Select("select count(1) from tbl_planworkorder where status=#{status}")
	Long countByPlanWorkOrderStatus(String status);

	@Select("select count(1) from tbl_planworkorder where status=#{status} and planDate=#{date}")
	Long countByPlanDateAndPlanWorkOrderStatus(String date, String status);

	@Select("select * from tbl_planworkorder where planDate=#{date}")
	List<PlanWorkOrder> findAllByPlanDate(String date);

	@Select("select count(1) from tbl_planworkorder")
	Long count();

	//又是一个可以复用但没那么好用的方法
	@Select("select count(1) from tbl_planworkorder ${conditions}")
	Long countByFilterConditions(String conditions);

	@Select("select * from tbl_planworkorder where planDate=#{date} and status=#{status}")
	List<PlanWorkOrder> findAllByPlanDateAndPlanWorkOrderStatus(String date, String status);

	@Select("select * from  (select * from tbl_planworkorder where planDate=#{date} and status=#{status}) plan_temp limit #{start},#{itemNum}")
	List<PlanWorkOrder> findAllByPlanDateAndPlanWorkOrderStatusAndPage(String date, String status, long start, long itemNum);
	
	@Select("select * from tbl_planworkorder where DATE_FORMAT(createTime, '%Y-%m-%d')=#{date}")
	List<PlanWorkOrder> countPlanWorkOrdersByDate(String date);
	
    @Select("select * from tbl_planworkorder where DATE_FORMAT(createTime, '%Y-%m-%d')=#{date} order by id DESC limit #{start},#{itemNum}")
	List<PlanWorkOrder> findByPageAndDate(String date,long start, long itemNum);

	@Select("select * from tbl_planworkorder where status=#{status}")
	List<PlanWorkOrder> findAllByPlanWorkOrderStatus(String status);

	//又有forklift_plan_temp
	@Select("select * from  (select * from tbl_planworkorder where status=#{status}) forklift_plan_temp limit #{start},#{itemNum}")
	List<PlanWorkOrder> findAllByPlanWorkOrderStatusAndPage(String status, long start, long itemNum);

	@Delete("delete from tbl_planworkorder where id = #{id}")
	public int deleteById(Long id);
	
	@Update("update tbl_planworkorder set isReleased=#{isReleased} where id = #{id}")
	public int updateReleasedFlag(int isReleased, long id);
	
	@Select("select isReleased from tbl_planworkorder where id=#{id}")
	public int findIsReleased(long id);
	
	@Select("select sequence from tbl_planworkorder where id=#{id}")
	public String fingSequence(long id);
	
	@Select("select id from tbl_planworkorder order by id ASC limit 1")
	public long fingMinId();
	
	@Select("select sequence from tbl_planworkorder order by sequence ASC limit 1")
	public String fingMinSequence();
		
	@Select("select id from tbl_planworkorder order by id DESC limit 1")
	public long fingMaxId();
	
	@Select("select sequence from tbl_planworkorder order by sequence DESC limit 1")
	public String fingMaxSequence();
}
